import pymysql
import openpyxl
import yaml
from faker import Faker



class DataBase:

    host = 'localhost'
    port = 3306
    database = 'finance'
    user = 'root'
    password = 'root'
    charset = 'utf8'

    def update(self, sql, param):
        con = pymysql.connect(
            host=self.host,
            port=self.port,
            user=self.user,
            password=self.password,
            database=self.database,
            charset=self.charset
        )
        cursor = con.cursor()
        cursor.execute(sql, param)
        con.commit()
        cursor.close()
        con.close()


    def select(self, sql, param):
        con = pymysql.connect(
            host=self.host,
            port=self.port,
            user=self.user,
            password=self.password,
            database=self.database,
            charset=self.charset
        )
        cursor = con.cursor()
        cursor.execute(sql, param)
        data = cursor.fetchall()
        con.commit()
        cursor.close()
        con.close()
        return data

class PyXLSX:

    def create_excel(self, xlsx_name):
        wb = openpyxl.Workbook(xlsx_name)
        wb.save(xlsx_name)

    def write_excel(self, xlsx_name, sheet_name, date):
        wb = openpyxl.load_workbook(xlsx_name)
        ws = wb[sheet_name]
        for row in date:
            ws.append(row)
        wb.save(xlsx_name)

    def read_excel(self, xlsx_name, sheet_name):

        wb = openpyxl.load_workbook(xlsx_name)
        ws = wb[sheet_name]
        date = []
        for row in ws.iter_rows(min_row=2):
            row_data = []
            for cell in row:
                row_data.append(cell.value)
            date.append(row_data)
        return date




class Data:
    #登入测试案例

    success_login = [['lisi', '123456', '个人理财系统'],
                          ['admin', '123456', '后台管理系统'],
                          ['lisi3', '123!@#$%^%&456', '个人理财系统'],
                          ['lisi4', '123456', '度小满理财-登录[内测版]'],
                          ['lisi', '1234567', '度小满理财-登录[内测版]'],
                          ['admin1', '123456', '度小满理财-登录[内测版]'],
                          ['admin', '1234567', '度小满理财-登录[内测版]']
                          ]

    # 注册测试案例
    success_register = [['lisi100', '123456', '123456', '度小满理财-登录[内测版]'],
                             ['lisi101', '666666', '666666', '个人理财系统注册界面'],
                             ['lisi102', '123456', '666666', '个人理财系统注册界面'],
                             ['lisi103', '123456', '', '个人理财系统注册界面'],
                             ['lisi104', '', '', '个人理财系统注册界面']
                             ]
    # 银行卡新增
    success_bank_insert = [['工商银行', '借记卡', '1123654789638521', '新增成功'],
                                ['工商银行', '借记卡', '11236547896385216', '新增失败'],
                                ['工商银行', '借记卡', '1123654789638521', '新增失败'],
                                ['工商银行', '信用卡', '1123654789638521', '新增失败']
                                ]
    # 银行卡删除
    success_bank_delete = ['删除成功', '取消删除']

    # 银行卡修改
    success_bank_update = [['中国银行', '借记卡', '1236547412589632', '修改失败'],
                                ['工商银行', '借记卡', '1123654789638521', '修改成功'],
                                ['工商银行', '借记卡', '11236547896385216', '修改失败'],
                                ['工商银行', '信用卡', '1236547412589632', '修改成功']
                                ]

    # 零钱理财
    success_money_self = [['lisi', '123456', '666666', '买入成功!'],
                               ['lisi', '123456', '6666667', '买入失败!'],
                               ['qonggui', '123456', '666666', '买入失败!，银行卡余额不足'],
                               ['xin', '123456', '666666', '买入失败!，请绑定银行卡']
                               ]
    # 期限理财
    success_money_time = [['lisi', '123456', '666666', '买入成功!'],
                               ['lisi', '123456', '6666667', '买入失败!'],
                               ['qonggui', '123456', '666666', '买入失败!，银行卡余额不足'],
                               ['xin', '123456', '666666', '买入失败!，请绑定银行卡']
                               ]
    # 基金理财
    success_money_ji = [['lisi', '123456', '666666', '买入成功!'],
                             ['lisi', '123456', '6666667', '买入失败!'],
                             ['qonggui', '123456', '666666', '买入失败!，银行卡余额不足'],
                             ['xin', '123456', '666666', '买入失败!，请绑定银行卡'],
                             ['sa', '123456', '666666', '买入失败!,请前往个人信息设置支付密码']
                             ]
    # 网贷申请
    success_loan_apply = [['lisi', '123456', '10000', '100', '申请成功!'],
                               ['lisi', '123456', '10000', '100', '申请成功!'],
                               ['xin', '123456', '100', '100', '申请失败!,请先绑定银行卡'],
                               ['lisi', '123456', '100000000000000', '100', '申请失败'],
                               ['lisi', '123456', '10', '100', '申请失败!'],
                               ['lisi', '123456', '10000', '366', '申请失败!'],
                               ['lisi', '123456', '10000', '365', '申请成功!']
                               ]
    # 网贷审核
    success_loan_approve = [['lisi', '123456', '10000', '100'],
                                 ['lisi', '123456', '10000', '100'],
                                 ['lisi', '123456', '10000', '365']
                                 ]

class Excel:

    def set_success_login(self, xlsx_name, sheet_name):
        data = PyXLSX().read_excel(xlsx_name, sheet_name)
        return data



d = Excel().set_success_login('222.xlsx', '注册')
print(d)